package com.minsu.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.minsu.entity.Listing;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;
import java.util.Map;

@Mapper
public interface ListingMapper extends BaseMapper<Listing> {

    @Select("SELECT l.district, COUNT(1) as count, AVG(li.price) as avgPrice " +
            "FROM locations l " +
            "JOIN listings li ON l.district = li.location " +  // 改为按地区名关联
            "GROUP BY l.district")
    List<Map<String, Object>> getDistrictStats();
    
    @Select("SELECT DATE_FORMAT(ph.date, '%Y-%m') as month, AVG(ph.price) as avgPrice " +
           "FROM price_history ph " +
           "GROUP BY DATE_FORMAT(ph.date, '%Y-%m') " +
           "ORDER BY month")
    List<Map<String, Object>> getPriceTrend();

    @Select("SELECT DISTINCT location FROM listings ORDER BY location")
    List<String> selectDistinctDistricts();
} 